<?php
/**
 * Created by Zhy
 * User: Zhy
 * Date: 2019/4/8
 * Time: 9:38
 */

namespace app\admin\controller;


use think\Db;
use think\Request;

class School extends Base
{
    /**
     * 学校首页展示
     * @return mixed|\think\response\Json
     */
    public function index(){
        if(request()->isAjax ()){
            $sc_name = input('sc_name');
            $where['sc_status'] = ['NEQ', 2];
            
            if(isset($sc_name) && $sc_name != ''){
                $where['sc_name'] = ['like', '%' . $sc_name . '%'];
            }
            
            $Nowpage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::name('school')->where($where)->count();// 获取总条数
            $lists = Db::name('school')
//                ->field('sc_id, sc_name, sc_status, sc_create_time, sc_update_time, sc_code')
                ->where($where)
                ->page($Nowpage,$limits)
                ->order('sc_update_time desc')
                ->select();
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        return $this->fetch();
    }
    
    /**
     * 添加学校
     * @return mixed|\think\response\Json
     */
    public function schoolAdd(){
        if(Request::instance()->isPost()){
            $sc_name = trim(input('sc_name'), ' ');
            $sc_code = trim(input('sc_code'), ' ');
            $sc_code1 = trim(input('sc_zhuguan'), ' ');
            $sc_code2 = trim(input('sc_city'), ' ');
            $sc_code3 = trim(input('sc_level'), ' ');
            $isHave = Db::table('up_school')->where('sc_name', $sc_name)->find();
            if($isHave){
                return json(['code' => 100, 'msg' => '该学校已存在']);
            }
            $isHave1 = Db::table('up_school')->where('sc_code', $sc_code)->find();
            if($isHave1){
                return json(['code' => 100, 'msg' => '该学校识别码已存在']);
            }
            
            $data = [
                'sc_name' => $sc_name,
                'sc_code' => $sc_code,
                'sc_zhuguan' => $sc_code1,
                'sc_city' => $sc_code2,
                'sc_level' => $sc_code3,
                'sc_create_time' => date('Y-m-d H:i:s'),
                'sc_update_time' => date('Y-m-d H:i:s')
            ];
            $res = Db::table('up_school')->insert($data);
            if($res){
                return json(['code' => 200, 'msg' => '添加成功']);
            }else{
                return json(['code' => 100, 'msg' => '添加失败']);
            }
        }
        return $this->fetch('school/schooladd');
    }
    
    /**
     * 编辑学校
     * @return mixed|\think\response\Json
     */
    public function schoolEdit(){
        if(Request::instance()->isPost()){
            $id = input('sc_id');
            $sc_name = trim(input('sc_name'), ' ');
            $sc_code = trim(input('sc_code'), ' ');
            $sc_code1 = trim(input('sc_zhuguan'), ' ');
            $sc_code2 = trim(input('sc_city'), ' ');
            $sc_code3 = trim(input('sc_level'), ' ');
            $isHave = Db::table('up_school')
                ->where('sc_name', $sc_name)
                ->where('sc_id', 'NEQ', $id)
                ->find();
            if($isHave){
                return json(['code' => 100, 'msg' => '该学校已存在']);
            }
            $isHave1 = Db::table('up_school')
                ->where('sc_code', $sc_code)
                ->where('sc_id', 'NEQ', $id)
                ->find();
            if($isHave1){
                return json(['code' => 100, 'msg' => '该学校识别码已存在']);
            }
            
            $update = [
                'sc_name' => $sc_name,
                'sc_code' => $sc_code,
                'sc_zhuguan' => $sc_code1,
                'sc_city' => $sc_code2,
                'sc_level' => $sc_code3,
                'sc_update_time' => date('Y-m-d H:i:s')
            ];
            $res = Db::table('up_school')->where('sc_id', $id)->update($update);
            if($res){
                return json(['code' => 200, 'msg' => '编辑成功']);
            }else{
                return json(['code' => 100, 'msg' => '编辑失败']);
            }
        }
        $sc_id = input('sc_id');
        $school = Db::table('up_school')
//            ->field('sc_name, sc_code')
            ->where('sc_id', $sc_id)
            ->find();
        $this->assign([
            'sc_id' => $sc_id,
            'school' => $school
        ]);
        return $this->fetch('school/schooledit');
    }
    
    /**
     * 上架
     * @return \think\response\Json
     */
    public function schoolOn(){
        $sc_id = input('id');
        $res = Db::table('up_school')->where('sc_id', $sc_id)->setField('sc_status', 1);
        if($res){
            return json(['code' => 200, 'msg' => '上架成功']);
        }else{
            return json(['code' => 200, 'msg' => '上架失败']);
        }
    }
    
    /**
     * 下架
     * @return \think\response\Json
     */
    public function schoolDown(){
        $sc_id = input('id');
        $res = Db::table('up_school')->where('sc_id', $sc_id)->setField('sc_status', 3);
        if($res){
            return json(['code' => 200, 'msg' => '下架成功']);
        }else{
            return json(['code' => 200, 'msg' => '下架失败']);
        }
    }
    
    /**
     * 删除
     * @return \think\response\Json
     */
    public function schoolDel(){
        $id = input('id');
        $res = Db::table('up_school')->where('sc_id', $id)->setField('sc_status', 2);
        if($res){
            return json(['code' => 200, 'msg' => '删除成功']);
        }else{
            return json(['code' => 100, 'msg' => '删除失败']);
        }
    }
    /**
     * 导入Excel
     * @return mixed
     */
    public function excelIn(){
        set_time_limit(0);
        if(request()->file('file')){
            $file = request()->file('file');
            $info  = $file->getInfo();
            $file_path = $info['tmp_name'];
            //取出文件后缀
            $file_type = explode(".",$info['name']);
            $file_type = end($file_type);
            vendor("PHPExcel.PHPExcel.IOFactory");
            if( $file_type == 'xls' ){
                $inputFileType = 'Excel5';    //这个是读 xls的
            }else{
                $inputFileType = 'Excel2007';//这个是计xlsx的
            }
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($file_path);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $highestRow = $objWorksheet->getHighestRow();
//            print_r($highestRow);die;
            $time = date('Y-m-d H:i:s');
            Db::startTrans();
            try{
                for ($i=3;$i<=$highestRow;$i++) {
                    $a = $objPHPExcel->getActiveSheet ()->getCell ("A" . $i)->getValue ();//获取A列的值
                    $b = $objPHPExcel->getActiveSheet ()->getCell ("B" . $i)->getValue ();//获取B列的值
                    $c = $objPHPExcel->getActiveSheet ()->getCell ("C" . $i)->getValue ();//获取C列的值
                    $d = $objPHPExcel->getActiveSheet ()->getCell ("D" . $i)->getValue ();//获取D列的值
                    $e = $objPHPExcel->getActiveSheet ()->getCell ("E" . $i)->getValue ();//获取E列的值
                    $f = $objPHPExcel->getActiveSheet ()->getCell ("F" . $i)->getValue ();//获取E列的值
                    if($a != '' && $d != '' && $e != ''){
                        $c = 'G_' . substr($c,-5,5);
                        //插入学校
                        $data = [
                            'sc_name' => $b,//学校名称
                            'sc_code' => $c,
                            'sc_create_time' => $time,
                            'sc_update_time' => $time,
                            'sc_zhuguan' => $d,//省
                            'sc_city' => $e,//市
                            'sc_level' => $f,//区
                        ];
                        $school_id = Db::table('up_school')->insert($data);
                    }
                }
                Db::commit();
                echo '/admin/School/index';
            }catch(\Exception $e){
                Db::rollback();
            }

        }
        return $this->fetch('school/excelin');
    }
}